Amazon Redshift: ソートキーを指定してないとVACUUMの恩恵に預かれないというお話(&VACUUM対象テーブル抽出 改良版SQL付)
小ネタです。
そして、言われてみれば確かにそうだよね、という割と初歩的且つシンプルなお話を。
過去のエントリにて、"VACUUM対象テーブルを洗い出す"というテーマで以下2本のエントリを投稿しました。
- Amazon Redshift Useful SQL: VACUUM処理が必要なテーブルを洗い出す ｜ Developers.IO
- Amazon Redshift: VACUUM処理が必要なテーブルを改めて洗い出す ｜ Developers.IO
上記SQLを使って、VACUUM処理を必要とするテーブルに対してVACUUM処理を実行して行く過程で、何度実行しても未ソート状態が改善されないテーブルが幾つかありました。
この事象は、下記袴田のエントリ内『統計情報の鮮度とソートされていない行の割合に注意！』で示されているSQLでも同様です。VACUUMすべき対象として挙がってくるものの、VACUUMを行っても状況は変わりませんでした。
原因は至極シンプルなものでした。『テーブルにソートキー指定がされてない』が為に、VACUUM処理を行ってもソート処理がされない、ただそれだけでした。分かってみれば『そらそうよ』(by 岡田彰布元阪神・オリックス監督)な内容です。
テーブルに対してDELETE等を行っていれば、その際のゴミデータをVACUUMで除去・改善出来るのだとは思いますが、ソート状態の改善についてはキーが指定されていないためにSKIPされている、という事なのでしょう。
VACUUM・ANALYZE処理が必要なテーブルを割り出すSQL(改良版)
上記内容を踏まえ、過去エントリの内容をよりブラッシュアップさせたSQLが以下となります。以下2つのテーブル情報を結合させています。
SELECT
to_vacuum_information.id,
to_vacuum_information.schema_name,
to_vacuum_information.table_name,
to_vacuum_information.rows,
to_vacuum_information.sorted_rows,
to_vacuum_information.sort_percentage,
to_vacuum_information.first_sortkey,
to_vacuum_information.to_set_sortkey,
CASE sort_percentage
WHEN 0.000 THEN '03_完全未SORT(要SORT)'
WHEN 1.000 THEN '05_完全SORT済み'
ELSE '04_一部未SORT(要SORT)'
END AS to_vacuum_sort,
to_vacuum_information.stats_off,
CASE to_vacuum_information.stats_off
WHEN 0.00 THEN ''
ELSE '06_要ANALYZE'
END AS to_analyze
FROM
(SELECT
sorted_infomation.id,
TRIM(svv_table_info.schema) AS schema_name,
sorted_infomation.table_name,
sorted_infomation.rows,
sorted_infomation.sorted_rows,
CASE sorted_infomation.sorted_rows
WHEN 0 THEN 0.000
ELSE
CAST(
CAST(sorted_infomation.sorted_rows AS DOUBLE PRECISION) / CAST(sorted_infomation.rows AS DOUBLE PRECISION)
AS DECIMAL(6,3)
)
END AS sort_percentage,
TRIM(svv_table_info.sortkey1) AS first_sortkey,
NVL2(TRIM(svv_table_info.sortkey1), '01_設定済', '00_未設定(要SORTKEY設定)') AS to_set_sortkey,
svv_table_info.stats_off
FROM
(SELECT
stv_tbl_perm.id,
TRIM(stv_tbl_perm.name) AS table_name,
SUM(stv_tbl_perm.rows) AS rows,
SUM(stv_tbl_perm.sorted_rows) AS sorted_rows,
stv_tbl_perm.db_id
FROM
stv_tbl_perm
GROUP BY
stv_tbl_perm.id,
TRIM(stv_tbl_perm.name),
stv_tbl_perm.db_id) sorted_infomation
INNER JOIN svv_table_info ON sorted_infomation.id = svv_table_info.table_id
) to_vacuum_information
WHERE
to_vacuum_information.schema_name = '<任意のスキーマ名>'
AND to_vacuum_information.table_name = '<任意のテーブル名>'
ORDER BY
to_set_sortkey ASC,
to_vacuum_information.sort_percentage ASC,
to_vacuum_information.rows DESC;
以下が実行結果のサンプルです。観点としては3つ。
id | schema_name | table_name | first_sortkey | to_set_sortkey | rows | sorted_rows | sort_percentage | to_vacuum_sort | stats_off | to_analyze --------+-------------+-----------------+-------------------+--------------------------+------------+-------------+-----------------+-----------------------+-----------+-------------- 100001 | schema_aa | table_aaa | | 00_未設定(要SORTKEY設定) | 904901515 | 0 | 0.000 | 02_完全未SORT(要SORT) | 0.00 | 100002 | schema_aa | table_bbb | | 00_未設定(要SORTKEY設定) | 48495810 | 0 | 0.000 | 02_完全未SORT(要SORT) | 0.00 | 100003 | schema_aa | table_ccc | | 00_未設定(要SORTKEY設定) | 9840944 | 0 | 0.000 | 02_完全未SORT(要SORT) | 0.00 | : 200001 | schema_bb | table_01 | xxx_id | 01_SORTKEY設定済 | 47 | 0 | 0.000 | 02_完全未SORT(要SORT) | 99.99 | 05_要ANALYZE 200002 | schema_bb | table_02 | yyyy_id | 01_SORTKEY設定済 | 36 | 0 | 0.000 | 02_完全未SORT(要SORT) | 0.00 | : 300001 | public | table_zz | aaa_date | 01_SORTKEY設定済 | 54829 | 6853 | 0.125 | 03_一部未SORT(要SORT) | 87.49 | 05_要ANALYZE 300002 | public | table_yy | bb_timestamp | 01_SORTKEY設定済 | 472 | 59 | 0.125 | 03_一部未SORT(要SORT) | 87.50 | 05_要ANALYZE 300004 | public | table_xx | ooo_id | 01_SORTKEY設定済 | 90 | 45 | 0.500 | 03_一部未SORT(要SORT) | 54.44 | 05_要ANALYZE 300005 | public | table_ww | uuid | 01_SORTKEY設定済 | 9434783 | 5940500 | 0.630 | 03_一部未SORT(要SORT) | 100.00 | 05_要ANALYZE 300006 | public | table_vv | ccc_date | 01_SORTKEY設定済 | 2373521 | 1742388 | 0.734 | 03_一部未SORT(要SORT) | 99.99 | 05_要ANALYZE 300007 | public | table_uu | ddd_timestamp | 01_SORTKEY設定済 | 169769039 | 161609366 | 0.952 | 03_一部未SORT(要SORT) | 0.00 | 300008 | public | table_tt | eee_id | 01_SORTKEY設定済 | 588380 | 586625 | 0.997 | 03_一部未SORT(要SORT) | 0.00 | 300009 | public | table_rr | ffff_key | 01_SORTKEY設定済 | 25032673 | 25005037 | 0.999 | 03_一部未SORT(要SORT) | 0.00 | : 900001 | bi_cm | table_abc | user_id | 01_SORTKEY設定済 | 4383172369 | 4383172369 | 1.000 | 04_完全SORT済み | 0.19 | 05_要ANALYZE 900002 | bi_cm | table_xyz | store_cd | 01_SORTKEY設定済 | 1015605084 | 1015605084 | 1.000 | 04_完全SORT済み | 0.00 | 900003 | bi_cm | table_xxx | xyz_date | 01_SORTKEY設定済 | 456895134 | 456895134 | 1.000 | 04_完全SORT済み | 0.00 |
1.テーブルのソートキー設定有無を確認
1つ目はテーブルのソートキー設定の有無。first_sortkeyとto_set_sortkey列の内容を確認します。ソートキー指定がされていないものにはfirst_sortkeyの値が設定されず、to_set_sortkeyの欄には『00_未設定(要SORTKEY設定)』というラベルが表示されます。、そもそもVACUUMしてもSORTKEYを使ってのソート処理がされない状態となるので、まずはテーブル定義を見直し適切なソートキーを設定するところから着手します。
2.テーブルのソート状態を確認
次いでテーブルのソート状態を見ます。sort_percentageとto_vacuum_sort列です。
ソート状態が0.000(%)、即ちソート済みの列が全く無い場合、VACUUM処理及びVACUUM SORT ONLYでソート処理を行う必要があります(『02_完全未SORT(要SORT)』)。1.000(=100%、100%ソート済み)に満たないもの(『03_一部未SORT(要SORT)』)についてもソート処理が必要です。Amazon Redshiftではソート対象の基準デフォルト値が95%以上のもの(未ソート状態のデータが5%に満たないもの)についてはソート処理をスキップしていますので、対象テーブルの中でソート済み状態が95%以上となっているものについては実施を保留としても良いかも知れません。
3.テーブルのANALYZE状態を確認
最後3つ目はテーブルの最新の統計情報が得られているかの確認です。stats_offとto_analyze列が該当します。『svv_table_info』テーブルのドキュメントを見ると、stats_offの値は『テーブルの統計情報の古さを示す数。0 は最新で、100 は最新でないことを示します。』という見方の解説があります。0で無いものについてはto_analyze列に『05_要ANALYZE』のラベルを表示させていますので、このラベルが表示されたテーブルについては『ANALYZE実行すべし』という事になります。
ソートキーの有無でVACUUMの効き目を確認
では、ソートキーの有無でVACUUMの実行内容がどう変わるのか確認してみましょう。以下のpublic.ordersテーブルはソートキー(sortkey)指定がありません。(全て0。1以上の数字が入っている場合、数字の順にソートキー指定となる)
# SELECT * FROM pg_table_def WHERE schemaname = 'public' AND tablename = 'orders'; schemaname | tablename | column | type | encoding | distkey | sortkey | notnull ------------+-----------+----------------------+------------------------+----------+---------+---------+--------- public | orders | order_id | integer | none | f | 0 | t public | orders | order_date | date | none | f | 0 | t public | orders | priority | character varying(12) | none | f | 0 | t public | orders | quantity | smallint | none | f | 0 | t public | orders | sales | double precision | none | f | 0 | f public | orders | discount_rate | double precision | none | f | 0 | f public | orders | ship_mode | character varying(20) | bytedict | f | 0 | t public | orders | profit | integer | none | f | 0 | t public | orders | unit_price | integer | none | f | 0 | t public | orders | ad_expenses | integer | none | f | 0 | t public | orders | shipping_cost | integer | none | f | 0 | t public | orders | customer_name | character varying(50) | lzo | f | 0 | t public | orders | prefecture | character varying(12) | none | f | 0 | t public | orders | city | character varying(20) | none | f | 0 | t public | orders | area | character varying(12) | none | f | 0 | t public | orders | shop_name | character varying(20) | none | f | 0 | t public | orders | customer_segment | character varying(30) | bytedict | f | 0 | t public | orders | product_category | character varying(30) | bytedict | f | 0 | t public | orders | product_sub_category | character varying(100) | lzo | f | 0 | t public | orders | product_id | character varying(10) | none | f | 0 | t public | orders | product_name | character varying(100) | lzo | f | 0 | t public | orders | product_description | character varying(200) | lzo | f | 0 | t public | orders | product_container | character varying(100) | lzo | f | 0 | t public | orders | base_margin | double precision | none | f | 0 | f public | orders | supplier | character varying(30) | none | f | 0 | t public | orders | deliver_date | date | none | f | 0 | t public | orders | ship_date | date | none | f | 0 | t (27 rows) # SELECT COUNT(*) FROM public.orders; count ------- 8369 (1 row)
これとは別に、ソートキー指定を行った同一内容別テーブル：public.orders_newを用意。
CREATE TABLE IF NOT EXISTS public.orders_new
(
order_id INTEGER NOT NULL
,order_date DATE NOT NULL
,priority VARCHAR(12) NOT NULL
,quantity SMALLINT NOT NULL
,sales DOUBLE PRECISION
,discount_rate DOUBLE PRECISION
,ship_mode VARCHAR(20) NOT NULL ENCODE bytedict
,profit INTEGER NOT NULL
,unit_price INTEGER NOT NULL
,ad_expenses INTEGER NOT NULL
,shipping_cost INTEGER NOT NULL
,customer_name VARCHAR(50) NOT NULL ENCODE lzo
,prefecture VARCHAR(12) NOT NULL
,city VARCHAR(20) NOT NULL
,area VARCHAR(12) NOT NULL
,shop_name VARCHAR(20) NOT NULL
,customer_segment VARCHAR(30) NOT NULL ENCODE bytedict
,product_category VARCHAR(30) NOT NULL ENCODE bytedict
,product_sub_category VARCHAR(100) NOT NULL ENCODE lzo
,product_id VARCHAR(10) NOT NULL
,product_name VARCHAR(100) NOT NULL ENCODE lzo
,product_description VARCHAR(200) NOT NULL ENCODE lzo
,product_container VARCHAR(100) NOT NULL ENCODE lzo
,base_margin DOUBLE PRECISION
,supplier VARCHAR(30) NOT NULL
,deliver_date DATE NOT NULL
,ship_date DATE NOT NULL
,PRIMARY KEY (order_id)
)
DISTSTYLE EVEN
SORTKEY (order_date, order_id);
CREATE
# INSERT INTO public.orders_new (SELECT * FROM public.orders ORDER BY profit DESC);
INSERT 0 8369
# INSERT INTO public.orders_new (SELECT * FROM public.orders_new ORDER BY shipping_cost, customer_name);
INSERT 0 8369
Time: 2552.128 ms
# SELECT COUNT(*) FROM public.orders_new;
count
-------
16738
(1 row)
確認用SQLで内容を確認してみます。
:
WHERE
to_vacuum_information.schema_name = 'public'
AND to_vacuum_information.table_name like'orders%'
ORDER BY
to_set_sortkey ASC,
to_vacuum_information.sort_percentage ASC,
to_vacuum_information.rows DESC;
id | schema_name | table_name | rows | sorted_rows | sort_percentage | first_sortkey | to_set_sortkey | to_vacuum_sort | stats_off | to_analyze
--------+-------------+----------------+---------+-------------+-----------------+---------------+--------------------------+-----------------------+-----------+--------------
553671 | public | orders | 8369 | 0 | 0.000 | | 00_未設定(要SORTKEY設定) | 03_完全未SORT(要SORT) | 99.99 | 06_要ANALYZE
553675 | public | orders_new | 16738 | 8369 | 0.500 | order_date | 01_設定済 | 04_一部未SORT(要SORT) | 99.99 | 06_要ANALYZE
(2 rows)
双方テーブルに対してVACUUM処理とANALYZE処理を実行。実行後の結果を改めて確認して見ると、ソートキー指定を行ったorders_newテーブルはちゃんと処理されていますが、未指定のordersは処理そのものがSKIPされている様です。(処理時間的に) ソートキー指定を行っていないのでそもそも並び替え出来ないよ、という事ですね。
# VACUUM public.orders;
VACUUM
Time: 806.793 ms
# ANALYZE public.orders;
ANALYZE
Time: 3498.997 ms
# VACUUM public.orders_new;
VACUUM
Time: 44527.891 ms
# ANALYZE public.orders_new;
ANALYZE
Time: 2223.968 ms
:
:
WHERE
to_vacuum_information.schema_name = 'public'
AND to_vacuum_information.table_name like'orders%'
ORDER BY
to_set_sortkey ASC,
to_vacuum_information.sort_percentage ASC,
to_vacuum_information.rows DESC;
id | schema_name | table_name | rows | sorted_rows | sort_percentage | first_sortkey | to_set_sortkey | to_vacuum_sort | stats_off | to_analyze
--------+-------------+----------------+---------+-------------+-----------------+---------------+--------------------------+-----------------------+-----------+--------------
553671 | public | orders | 8369 | 0 | 0.000 | | 00_未設定(要SORTKEY設定) | 03_完全未SORT(要SORT) | 0.00 |
553675 | public | orders_new | 16738 | 16738 | 1.000 | order_date | 01_設定済 | 05_完全SORT済み | 0.00 |
(2 rows)
まとめ
という訳で、
r ‐､ | ○ | r‐‐､ 良い子の諸君！ _,;ﾄ - ｲ、 ∧l☆│ ∧ （⌒` ⌒ヽ /,ﾄ.-ｲ/,l Redshiftでテーブル作成する時は |ヽ ~~⌒γ⌒） r'⌒｀!´ `⌒） 忘れずにソートキー指定を行っておこう。 │ ヽー―'^ー-' （ ⌒γ⌒~~ /| (&分散キーの検討も忘れずに) │ 〉 | │ |｀ー^ｰ― r'| │ /───────| | |/ | l ﾄ､| でないとVACUUMしても | irｰ-､ ｰ ,} | / i データがちゃんと整理されないぞ。 | / ｀X´ ヽ / 入 |
こちらからは以上です。
![[アップデート] Control Tower ランディングゾーンがAWS Backup の統合管理をサポートしたので使ってみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-fff4d7e7c3509d1ffcc67578673c5f00/056494cb8aea27071cd8ed8a870bda83/aws-control-tower)
